package com.xl.rentkeeper.db.orm;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;


import com.xl.rentkeeper.db.orm.dsl.Column;
import com.xl.rentkeeper.db.orm.dsl.NotNull;
import com.xl.rentkeeper.db.orm.dsl.Unique;
import com.xl.rentkeeper.db.orm.dsl.UniqueConstraints;
import com.xl.rentkeeper.db.orm.util.NamingHelper;
import com.xl.rentkeeper.db.orm.util.NumberComparator;
import com.xl.rentkeeper.db.orm.util.ReflectionUtil;
import com.xl.rentkeeper.db.orm.util.TypeBuilder;
import com.xl.rentkeeper.log.QLog;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

public class SchemaGenerator {

    private Context context;

    public SchemaGenerator(Context context) {
        this.context = context;
    }

    public void createDatabase(SQLiteDatabase sqLiteDatabase) {
        Map<String, Class> tables = ORMap.getTables();
        for (Map.Entry<String, Class> entry : tables.entrySet()) {
            String tableName = entry.getKey();
            Class s = entry.getValue();
            createTable(tableName, s, sqLiteDatabase);
        }
    }

    public void doUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        Map<String, Class> tables = ORMap.getTables();
        for (Map.Entry<String, Class> entry : tables.entrySet()) {
            String tableName = entry.getKey();
            Class s = entry.getValue();
            try {  // we try to do a select, if fails then (?) there isn't the table
                sqLiteDatabase.query(tableName, null, null, null, null, null, null);
            } catch (SQLiteException e) {
                Log.i("Sugar", String.format("Creating table on update (error was '%s')",
                        e.getMessage()));
                createTable(tableName, s, sqLiteDatabase);
            }
        }
        executeSugarUpgrade(sqLiteDatabase, oldVersion, newVersion);
    }

    public void deleteTables(SQLiteDatabase sqLiteDatabase) {
        Map<String, Class> tables = ORMap.getTables();
        for (Map.Entry<String, Class> entry : tables.entrySet()) {
            String tableName = entry.getKey();
            Class s = entry.getValue();
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + NamingHelper.toSQLName(s));

        }
    }

    private boolean executeSugarUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        boolean isSuccess = false;
        checkColumnChange("", db);
        try {
            List<String> files = Arrays.asList(this.context.getAssets().list("sugar_upgrades"));
            Collections.sort(files, new NumberComparator());
            for (String file : files) {
                Log.i("Sugar", "filename : " + file);
                try {
                    int version = Integer.valueOf(file.replace(".sql", ""));
                    if ((version > oldVersion) && (version <= newVersion)) {
                        executeScript(db, file);
                        isSuccess = true;
                    }
                } catch (NumberFormatException e) {
                    Log.i("Sugar", "not a sugar script. ignored." + file);
                }

            }
        } catch (IOException e) {
            Log.e("Sugar", e.getMessage());
        }
        return isSuccess;
    }

    private void executeScript(SQLiteDatabase db, String file) {
        try {
            InputStream is = this.context.getAssets().open("sugar_upgrades/" + file);
            BufferedReader reader = new BufferedReader(new InputStreamReader(is));
            String line;
            while ((line = reader.readLine()) != null) {
                Log.i("Sugar script", line);
                db.execSQL(line.toString());
            }
        } catch (IOException e) {
            Log.e("Sugar", e.getMessage());
        }

        Log.i("Sugar", "Script executed");
    }

    public void createTable(String name, Class<?> table, SQLiteDatabase sqLiteDatabase) {
        List<Field> fields = ReflectionUtil.getTableFields(table);
        String tableName = name;
        String keyId = NamingHelper.toSQLNameDefault(ORMap.TableKeyId);
        StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        sb.append(tableName).append(" ( "+keyId+" INTEGER PRIMARY KEY AUTOINCREMENT ");

        for (Field column : fields) {
            String columnName = NamingHelper.toSQLName(column);
            String columnType = TypeBuilder.getColumnType(column.getType());

            if (columnType != null) {
                if (columnName.equalsIgnoreCase(keyId)) {
                    continue;
                }

                if (column.isAnnotationPresent(Column.class)) {
                    Column columnAnnotation = column.getAnnotation(Column.class);
                    columnName = columnAnnotation.name();

                    sb.append(", ").append(columnName).append(" ").append(columnType);

                    if (columnAnnotation.notNull()) {
                        if (columnType.endsWith(" NULL")) {
                            sb.delete(sb.length() - 5, sb.length());
                        }
                        sb.append(" NOT NULL");
                    }

                    if (columnAnnotation.unique()) {
                        sb.append(" UNIQUE");
                    }

                } else {
                    sb.append(", ").append(columnName).append(" ").append(columnType);

                    if (column.isAnnotationPresent(NotNull.class)) {
                        if (columnType.endsWith(" NULL")) {
                            sb.delete(sb.length() - 5, sb.length());
                        }
                        sb.append(" NOT NULL");
                    }

                    if (column.isAnnotationPresent(Unique.class)) {
                        sb.append(" UNIQUE");
                    }
                }
            }
        }

        if (table.isAnnotationPresent(UniqueConstraints.class)) {
            UniqueConstraints constraints = (UniqueConstraints) table.getAnnotation(UniqueConstraints.class);
            String columnName = constraints.columnNames();
            sb.append(",UNIQUE(" + columnName + ")");
            String clause = constraints.clause().toString();
            sb.append(" ON CONFLICT " + clause);
        }
        sb.append(" ) ");
        Log.i("Sugar", "Creating table " + tableName + " sql:" + sb.toString());

        if (!"".equals(sb.toString())) {
            try {
                sqLiteDatabase.execSQL(sb.toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void checkColumnChange(String packageName, SQLiteDatabase db) {
        long start = System.currentTimeMillis();
        String sql = "select tbl_name,sql from sqlite_master where type='table'";
        Cursor c = db.rawQuery(sql, null);
        List<String> allStatement = new ArrayList<String>();
        if (c != null) {
            while (c.moveToNext()) {
                String table = c.getString(0);
                String sqlS = c.getString(1);
                QLog.d("db.checkColumnChange", "table:" + table + " sql:" + sqlS);
                Class clz = ORMap.getTables().get(table);
                if (clz == null) {
                    continue;
                }
                String[] ss = sqlS.split(",");
                List<String> dbNames = new ArrayList<>();
                for (int i = 1; i < ss.length; i++) {
                    String str = ss[i].trim();
                    String[] ss1 = str.split(" ");
                    String fieldName = ss1[0];
                    dbNames.add(fieldName);
                }
                List<Field> fieldList = ReflectionUtil.getTableFields(clz);
                for (Field field : fieldList) {
                    boolean find = false;
                    String fName = NamingHelper.toSQLName(field);
                    if (fName.equalsIgnoreCase(ORMap.TableKeyId))
                        continue;
                    for (String dbName : dbNames) {
                        if (fName.equals(dbName)) {
                            find = true;
                            break;
                        }
                    }
                    if (!find) { // should add
                        String sqladd = addColumn(table, NamingHelper.toSQLName(field),
                                TypeBuilder.getColumnType(field.getType()));
                        allStatement.add(sqladd);
                    }
                }
            }
            c.close();
        }
        db.beginTransaction();
        try {
            for (String string : allStatement) {
                QLog.d("db.checkColumnChange", "exec sql:" + string);
                db.execSQL(string);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        QLog.d("db.checkColumnChange", "elapse:" + (System.currentTimeMillis() - start));
    }

    public static String addColumn(String tableName, String columnName, String clumnType) {
        String sql;
        sql = "alter table " + tableName + " add " + columnName + " " + clumnType;
        return sql;
    }
}
